package com.system.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.system.demo.entity.DTO.CompetitionFileDTO;
import com.system.demo.entity.database.Cognizance;
import com.system.demo.entity.mailPage.CognizanceVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;


public interface CognizanceMapper extends BaseMapper<Cognizance> {

    // 根据 application_id 列表批量查询认定成功的文件 URL
    @Select({
            "<script>",
            "SELECT cf.file_url, sa.id FROM competition_file cf",
            "JOIN submitapplication sa ON cf.application_id = sa.id",
            "WHERE sa.status = 5 AND sa.id IN",
            "<foreach item='item' index='index' collection='applicationIds' open='(' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            "</script>"
    })
    List<CompetitionFileDTO> selectFilesUrlByApplicationIds(@Param("applicationIds") List<Integer> applicationIds);


    @Select("<script>" +
            "SELECT " +
            "  COALESCE(p.count, 0) AS count, " +
            "  COALESCE(p.national, 0) AS national, " +
            "  COALESCE(p.provincial, 0) AS provincial, " +
            "  COALESCE(p.school, 0) AS school " +
            "FROM (" +
            "  SELECT " +
            "    COUNT(*) AS count, " +
            "    SUM(CASE WHEN final_rank_name = 0 THEN 1 ELSE 0 END) AS national, " +
            "    SUM(CASE WHEN final_rank_name = 1 THEN 1 ELSE 0 END) AS provincial, " +
            "    SUM(CASE WHEN final_rank_name = 2 THEN 1 ELSE 0 END) AS school " +
            "  FROM " +
            "    cognizance " +
            "  WHERE application_id IN " +
            "    <foreach item='id' index='index' collection='competitionIds' open='(' separator=',' close=')'>" +
            "      #{id}" +
            "    </foreach>" +
            ") p " +
            "RIGHT JOIN (SELECT 1) AS dummy ON TRUE" +
            "</script>")
    CognizanceVO selectRankCounts(@Param("competitionIds") List<Integer> competitionIds);


    @Select("<script>" +
            "SELECT " +
            "  COALESCE(COUNT(*), 0) AS count, " +
            "  COALESCE(SUM(CASE WHEN final_rank_name = 0 THEN 1 ELSE 0 END), 0) AS national, " +
            "  COALESCE(SUM(CASE WHEN final_rank_name = 1 THEN 1 ELSE 0 END), 0) AS provincial, " +
            "  COALESCE(SUM(CASE WHEN final_rank_name = 2 THEN 1 ELSE 0 END), 0) AS school " +
            "FROM cognizance " +
            "</script>")
    CognizanceVO selectRankCountsByAdmin();
}
